import psycopg2
from datetime import datetime

# 数据库连接配置
DB_CONFIG = {
    "dbname": "game_trading_v2",
    "user": "postgres",
    "password": "145114",
    "host": "localhost",
    "port": "5432"
}

def get_conn():
    """获取数据库连接"""
    return psycopg2.connect(**DB_CONFIG)

def search_equipments(search_text):
    """
    通过装备ID或装备名称搜索装备 - 使用ActiveEquipmentView
    
    参数:
        search_text: 可以是装备ID或名称的关键词
        
    返回:
        装备信息列表，包含更多字段: [(EquipmentID, EquipmentName, Category, Level, Rarity, Price, ListedDate, Status), ...]
    """
    conn = get_conn()
    cur = conn.cursor()
    try:
        # 尝试将搜索关键词转换为整数（装备ID）
        is_id_search = False
        try:
            equipment_id = int(search_text.strip())
            is_id_search = True
        except (ValueError, TypeError):
            pass
            
        if is_id_search:
            # 按装备ID精确搜索，使用ActiveEquipmentView
            cur.execute("""
                SELECT EquipmentID, EquipmentName, Category, Level, Rarity, Price, ListedDate, 'active' as Status
                FROM ActiveEquipmentView 
                WHERE EquipmentID = %s
            """, (equipment_id,))
        else:
            # 按装备名称模糊搜索，使用ActiveEquipmentView
            cur.execute("""
                SELECT EquipmentID, EquipmentName, Category, Level, Rarity, Price, ListedDate, 'active' as Status
                FROM ActiveEquipmentView 
                WHERE EquipmentName ILIKE %s
                ORDER BY ListedDate DESC
            """, (f'%{search_text}%',))
        
        return cur.fetchall()
    except Exception as e:
        print(f"搜索装备错误: {e}")
        return []
    finally:
        cur.close()
        conn.close()

def filter_equipments(category=None, min_level=None, max_level=None, rarity=None, search_text=None):
    """
    根据多条件筛选装备 - 使用ActiveEquipmentView
    """
    conn = get_conn()
    cur = conn.cursor()
    
    # 构建查询条件
    conditions = []
    params = []
    
    if search_text:
        # 尝试将搜索文本解析为ID
        try:
            equipment_id = int(search_text.strip())
            conditions.append("(EquipmentID = %s OR EquipmentName ILIKE %s)")
            params.extend([equipment_id, f'%{search_text}%'])
        except (ValueError, TypeError):
            conditions.append("EquipmentName ILIKE %s")
            params.append(f'%{search_text}%')
    
    if category and category != "全部":
        conditions.append("Category = %s")
        params.append(category)
        
    if min_level is not None:
        conditions.append("Level >= %s")
        params.append(min_level)
        
    if max_level is not None:
        conditions.append("Level <= %s")
        params.append(max_level)
        
    if rarity and rarity != "全部":
        conditions.append("Rarity = %s")
        params.append(rarity)
    
    # 构建SQL查询语句
    query = """
        SELECT EquipmentID, EquipmentName, Category, Level, Rarity, Price, ListedDate, 'active' as Status
        FROM ActiveEquipmentView
    """
    
    if conditions:
        query += f" WHERE {' AND '.join(conditions)}"
    
    query += " ORDER BY ListedDate DESC"
    
    try:
        cur.execute(query, tuple(params))
        return cur.fetchall()
    except Exception as e:
        print(f"筛选装备错误: {e}")
        return []
    finally:
        cur.close()
        conn.close()

def get_equipment_categories():
    """
    获取所有装备类别 - 使用ActiveEquipmentView
    
    返回:
        装备类别列表，如 ["武器", "防具", "消耗品", ...]
    """
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("SELECT DISTINCT Category FROM ActiveEquipmentView WHERE Category IS NOT NULL ORDER BY Category")
        categories = [row[0] for row in cur.fetchall()]
        return ["全部"] + categories  # 添加"全部"选项
    except Exception as e:
        print(f"获取装备类别错误: {e}")
        return ["全部"]
    finally:
        cur.close()
        conn.close()

def get_equipment_rarities():
    """
    获取所有稀有度 - 使用ActiveEquipmentView
    
    返回:
        稀有度列表，如 ["普通", "稀有", "传说", ...]
    """
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("SELECT DISTINCT Rarity FROM ActiveEquipmentView WHERE Rarity IS NOT NULL ORDER BY Rarity")
        rarities = [row[0] for row in cur.fetchall()]
        return ["全部"] + rarities  # 添加"全部"选项
    except Exception as e:
        print(f"获取稀有度错误: {e}")
        return ["全部"]
    finally:
        cur.close()
        conn.close()

def get_equipment_level_range():
    """
    获取装备等级范围 - 使用ActiveEquipmentView
    
    返回:
        (最小等级, 最大等级) 的元组
    """
    conn = get_conn()
    cur = conn.cursor()
    try:
        cur.execute("SELECT MIN(Level), MAX(Level) FROM ActiveEquipmentView WHERE Level IS NOT NULL")
        result = cur.fetchone()
        if result and result[0] is not None and result[1] is not None:
            return (result[0], result[1])
        return (1, 100)  # 默认范围
    except Exception as e:
        print(f"获取装备等级范围错误: {e}")
        return (1, 100)  # 默认范围
    finally:
        cur.close()
        conn.close()